const express = require('express');
const db = require('../db');
const md5=require('../util/md5')
//导入判断目录是否已经存在的处理模块
const mkdir = require('../util/mkdirs')
//引入中间件
const multer = require('multer');
var fs = require('fs');
  const path = require('path'); // 导入 path 模块
const router = express.Router();
//使用multer解析FormData数据
const storage = multer.memoryStorage(); // 将文件存储在内存中
const upload = multer({ storage: storage });
//登录验证jwt包
const jwt = require("jsonwebtoken");
//简易随机生成
const secretKey = 'weiyinglin';
const moment = require('moment');
const schedule = require('node-schedule');
const io = require("socket.io-client")
const socket = io();

//根据手机号查询咨询师的信息
router.get('/getconsultantInfoByaccount',(req,res)=>{
    const {account}=req.query
    const selectQuery='SELECT * FROM consultants WHERE account=?'
    db.query(selectQuery,[account],(err,rows)=>{
        if (err) {
            console.error('Error inserting user to database', err);
            return res.status(500).json({ message: '服务器错误' });
          } 
        return res.status(201).json(rows[0])
    })
})

//咨询师发布文章接口
router.post('/addArticle',upload.none(),(req,res)=>{
    const {title,content,picture,author}=req.body
    // console.log(title,content,picture,author)
    const status=1
    const insertQuery='INSERT INTO article (title,content,author,picture,status) VALUES (?,?,?,?,?)'
    db.query(insertQuery,[title,content,author,picture,status],(err,row)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      else{
        if(row.affectedRows>0){
          res.status(201).json({ message: '发布成功！' });
        }
        else{
          return res.status(402).json({error:'发布失败！'})
        }
      }
    })
})

//根据咨询师的姓名查询发布的所有文章
router.get('/getAllArticleByConsultant',(req,res)=>{
    const{author}=req.query
    const selectQuery='SELECT * FROM article WHERE author=?  ORDER BY created_at DESC '
    db.query(selectQuery,[author],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      return res.status(201).json(rows)
    })
})

//根据文章id删除对应文章接口
router.post('/delArticle',(req,res)=>{
    const {id}=req.body
    const deleteQuery='DELETE  from article WHERE id = ?'
    db.query(deleteQuery,[id],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      else{
        if(rows.affectedRows>0){
          return res.status(201).json({message:'删除成功'})
        }
        else{
          return res.status(402).json({error:'删除失败'})
        }
      }
    })
})

//根据文章id获取文章详情
router.get('/getArticleByid',(req,res)=>{
    const {id}=req.query
    const selectQuery='SELECT * from article WHERE id =?'
    db.query(selectQuery,[id],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      return res.status(201).json(rows[0])
    })
})

//根据文章id修改文章的信息接口
router.post('/updateArticleByid',upload.none(),(req,res)=>{
    const {id,title,picture,content}=req.body
    const updateQuery='UPDATE article SET title=?,picture=?,content=? WHERE id =?'
    db.query(updateQuery,[title,picture,content,id],(err,rows)=>{
      if (err) {
        console.error('Error inserting user to database', err);
        return res.status(500).json({ message: '服务器错误' });
      } 
      else{
        if(rows.affectedRows>0){
          return res.status(201).json({message:'修改成功'})
        }
        else{
          return res.status(402).json({error:'修改失败'})
        }
      }
    })
})

//咨询师修改个人名片接口
router.post('/updateConsultantInfo',upload.none(),(req,res)=>{
    const {id,name,avatar,coun_label,introduction,experience,gender}=req.body
    const updateQuery='UPDATE consultants SET avatar=?, name=?,coun_label=?,introduction=?,experience=?,gender=? WHERE id =?'
    db.query(updateQuery,[avatar,name,coun_label,introduction,experience,gender,id],(err,rows)=>{
        if (err) {
            console.error('Error inserting user to database', err);
            return res.status(500).json({ message: '服务器错误' });
          } 
          else{
            if(rows.affectedRows>0){
              return res.status(201).json({message:'修改成功'})
            }
            else{
              return res.status(402).json({error:'修改个人信息失败'})
            }
          }
    })
})

//查询数据库中设定的预约时间段
router.get('/gettimeperiods',(req,res)=>{
    const selectQuery='SELECT * FROM time_periods '
    db.query(selectQuery,[],(err,rows)=>{
        if (err) {
            console.error('Error inserting user to database', err);
            return res.status(500).json({ message: '服务器错误' });
        } 
        const formattedTimePeriods = rows.map((row) => {
            return `${row.start_time}-${row.end_time}`;
        });
        return res.status(200).json(formattedTimePeriods);
    })
})

//根据选择的日期查询空闲表中存在的空闲时间段
router.get('/getavailableTimeBydate',(req,res)=>{
    const {id,date}=req.query
    const  selectQuery='SELECT * FROM consultant_schedule WHERE coun_id=? AND DATE(start_time)=?AND start_time>NOW()'
    db.query(selectQuery,[id,date],(err,rows)=>{
        if (err) {
            console.error('Error inserting user to database', err);
            return res.status(500).json({ message: '服务器错误' });
          } 
              //对时间进行格式处理
        const timeDatas= rows.map(row=>{
        const date=row.start_time.toLocaleString().split(' ')[0];
        const timePart_start=row.start_time.toLocaleString().split(' ')[1]
        const timePart_end=row.end_time.toLocaleString().split(' ')[1]
        // 再次拆分时间部分并获取小时和分钟部分
       var timeParts_start = timePart_start.split(":");
       var timeParts_end = timePart_end.split(":");
       var timePartData_start = timeParts_start[0] + ":" + timeParts_start[1]; // 只获取小时和分钟    
       var timePartData_end = timeParts_end[0] + ":" + timeParts_end[1];
        return `${timePartData_start}-${timePartData_end}`
      })
          res.status(201).json(timeDatas)
    })
})

//咨询师添加空闲时间段接口
router.post('/addAvailableTimeBycoun_id',upload.none(),(req,res)=>{
    const {coun_id,date,timeSlot}=req.body
    const [startTime, endTime] = timeSlot.split('-');
    const dateTimeString1 = `${date} ${startTime}:00`;
    const dateTimeString2 = `${date} ${endTime}:00`;
    const start_time = new Date(dateTimeString1);
    const end_time = new Date(dateTimeString2);
    const insertQuery='INSERT INTO consultant_schedule (coun_id,start_time,end_time) VALUES (?,?,?)'
    db.query(insertQuery,[coun_id,start_time,end_time],(err,rows)=>{
        if (err) {
            console.error('Error inserting user to database', err);
            return res.status(500).json({ message: '服务器错误' });
          } 
          res.status(201).json({ message: '添加成功！' });
    })
})

//咨询师获取用户预约记录信息
router.get('/getAppointmentInfoBycoun_id',(req,res)=>{
    const {coun_id}=req.query
    const selectQuery="SELECT appointments.*, users.* FROM appointments INNER JOIN users ON appointments.user_id=users.id WHERE appointments.counselor_id=? ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
    db.query(selectQuery,[coun_id],(err,rows)=>{
        if (err) {
          console.error('Error inserting user to database', err);
          return res.status(500).json({ message: '服务器错误' });
        } 
        // console.log(rows)
        rows.forEach((row)=>{
            if(row.status=='Scheduled'){
              const currentDate=new Date()
              const targetDate=new Date(row.date)
              const updateQuery="UPDATE appointments SET status='Completed' WHERE appointment_id=?"
              if(currentDate>targetDate){
                 db.query(updateQuery,[row.appointment_id],(err,rows)=>{
                   if (err) {
                     console.error('Error inserting user to database', err);
                     return res.status(500).json({ message: '服务器错误' });
                   } 
                 })
                 row.status='Completed'
              } 
            }
            delete row.password
            const imagePath = path.join(__dirname, `../${row.avatar}`);  // 图片的实际存储路径
            //分片发送base64格式图片
            if (fs.existsSync(imagePath)) {
             // // 读取文件并将其转换为Base64格式
             const imageBuffer = fs.readFileSync(imagePath);
             const base64Image = Buffer.from(imageBuffer).toString('base64');
                // 按固定大小分割 Base64 数据
             const chunkSize = 1024; // 每个分块的大小
             const totalChunks = Math.ceil(base64Image.length / chunkSize);
             const base64Chunks = [];
             for (let i = 0; i < totalChunks; i++) {
              const start = i * chunkSize;
              const end = start + chunkSize;
              const chunk = base64Image.substring(start, end);
              base64Chunks.push(chunk);
             }
             row.avatar={ totalChunks, base64Chunks }
            }
            const formattedDate = moment(row.created_at).format('YYYY-MM-DD');
            row.created_at=formattedDate
        })
        return res.status(201).json(rows)
      })
})

//根据起始日期和结束日期搜索用户预约咨询记录
router.get('/getappo_infoBystart_endTime',(req,res)=>{
    const {coun_id,startTime,endTime}=req.query
    const selectQuery="SELECT appointments.*, users.* FROM appointments INNER JOIN users ON appointments.user_id=users.id WHERE appointments.counselor_id=? AND STR_TO_DATE(appointments.date, '%Y-%m-%d') BETWEEN STR_TO_DATE(?, '%Y-%m-%d') AND STR_TO_DATE(?, '%Y-%m-%d') ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
    db.query(selectQuery,[coun_id,startTime,endTime],(err,rows)=>{
        if (err) {
          console.error('Error inserting user to database', err);
          return res.status(500).json({ message: '服务器错误' });
        } 
        // console.log(rows)
        rows.forEach((row)=>{
            if(row.status=='Scheduled'){
              const currentDate=new Date()
              const targetDate=new Date(row.date)
              const updateQuery="UPDATE appointments SET status='Completed' WHERE appointment_id=?"
              if(currentDate>targetDate){
                 db.query(updateQuery,[row.appointment_id],(err,rows)=>{
                   if (err) {
                     console.error('Error inserting user to database', err);
                     return res.status(500).json({ message: '服务器错误' });
                   } 
                 })
                 row.status='Completed'
              } 
            }
            delete row.password
            const imagePath = path.join(__dirname, `../${row.avatar}`);  // 图片的实际存储路径
            //分片发送base64格式图片
            if (fs.existsSync(imagePath)) {
             // // 读取文件并将其转换为Base64格式
             const imageBuffer = fs.readFileSync(imagePath);
             const base64Image = Buffer.from(imageBuffer).toString('base64');
                // 按固定大小分割 Base64 数据
             const chunkSize = 1024; // 每个分块的大小
             const totalChunks = Math.ceil(base64Image.length / chunkSize);
             const base64Chunks = [];
             for (let i = 0; i < totalChunks; i++) {
              const start = i * chunkSize;
              const end = start + chunkSize;
              const chunk = base64Image.substring(start, end);
              base64Chunks.push(chunk);
             }
             row.avatar={ totalChunks, base64Chunks }
            }
            const formattedDate = moment(row.created_at).format('YYYY-MM-DD');
            row.created_at=formattedDate
        })
        return res.status(201).json(rows)
      })
})
//获取咨询师预约状态为scheduled并且时间在今天以后得信息接口
router.get('/getScheduleConsultantDataAfter',(req,res)=>{
    const {coun_id}=req.query
    // console.log(coun_id)
    const selectQuery="SELECT appointments.*, users.* FROM appointments INNER JOIN users ON appointments.user_id=users.id WHERE appointments.counselor_id=? AND appointments.status='Scheduled' AND appointments.date>=CURDATE() ORDER BY STR_TO_DATE(appointments.created_at, '%Y-%m-%d %H:%i:%s') DESC"
  db.query(selectQuery,[coun_id],(err,rows)=>{
    if (err) {
      console.error('Error inserting user to database', err);
      return res.status(500).json({ message: '服务器错误' });
    } 
    rows.forEach((row)=>{
        const imagePath = path.join(__dirname, `../${row.avatar}`);  // 图片的实际存储路径
        //分片发送base64格式图片
        if (fs.existsSync(imagePath)) {
         // // 读取文件并将其转换为Base64格式
         const imageBuffer = fs.readFileSync(imagePath);
         const base64Image = Buffer.from(imageBuffer).toString('base64');
            // 按固定大小分割 Base64 数据
         const chunkSize = 1024; // 每个分块的大小
         const totalChunks = Math.ceil(base64Image.length / chunkSize);
         const base64Chunks = [];
         for (let i = 0; i < totalChunks; i++) {
          const start = i * chunkSize;
          const end = start + chunkSize;
          const chunk = base64Image.substring(start, end);
          base64Chunks.push(chunk);
         }
         row.avatar={ totalChunks, base64Chunks }
        }
        const formattedDate = moment(row.created_at).format('YYYY-MM-DD');
        row.created_at=formattedDate
    })
    return res.status(201).json(rows)
  })
})

//根据预约号id来查询对应用户信息
router.get('/getUserInfoByAppointment_id',(req,res)=>{
  const {appointment_id}=req.query 
  const selectQuery='SELECT * FROM users  WHERE id=(SELECT user_id FROM appointments WHERE appointment_id=? )'
  db.query(selectQuery,[appointment_id],(err,rows)=>{
      if (err) {
          console.error('Error inserting user to database', err);
          return res.status(500).json({ message: '服务器错误' });
        } 
        delete rows[0].password
        const imagePath = path.join(__dirname, `../${rows[0].avatar}`);  // 图片的实际存储路径
        //分片发送base64格式图片
        if (fs.existsSync(imagePath)) {
         // // 读取文件并将其转换为Base64格式
         const imageBuffer = fs.readFileSync(imagePath);
         const base64Image = Buffer.from(imageBuffer).toString('base64');
            // 按固定大小分割 Base64 数据
         const chunkSize = 1024; // 每个分块的大小
         const totalChunks = Math.ceil(base64Image.length / chunkSize);
         const base64Chunks = [];
         for (let i = 0; i < totalChunks; i++) {
          const start = i * chunkSize;
          const end = start + chunkSize;
          const chunk = base64Image.substring(start, end);
          base64Chunks.push(chunk);
         }
         rows[0].avatar={ totalChunks, base64Chunks }
        }
        // console.log(rows[0])
      return res.status(201).json(rows[0])

  })
})

//根据预约号id来查询对应的用户id
router.get('/getconsultantIdByappointment_id',(req,res)=>{
  const {appointment_id}=req.query
  const selectQuery='SELECT counselor_id FROM appointments WHERE appointment_id=?'
  db.query(selectQuery,[appointment_id],(err,rows)=>{
      if (err) {
          console.error('Error inserting user to database', err);
          return res.status(500).json({ message: '服务器错误' });
        } 
      return res.status(201).json(rows[0])

  })
})


module.exports=router